*** ------------------------------------------------------------------***;
*** From this point on I'm only adding financial ratio from Compustat ***;
***			(for comparison with loan covenants)  					  ***;
*** ------------------------------------------------------------------***;
/***
 Construct Basic variables from Compustat 

>>> NOTE: I've already applied the filer below when I download the data from WRDS using SAS rssubmit;

>>> WRDS SAS download
rsubmit;
proc sql;
  	create table Compy1 as 
  	select *
	from compa.fundq (where=(INDFMT= 'INDL' and DATAFMT='STD' and POPSRC='D' and CONSOL='C')) 
	order by gvkey, datadate
	;
quit;
proc download data=Compy1 out=mycomp.fundq;
run;
endrsubmit;
signoff;

>>> NOTE: In the code below, "mycomp.fundq" refers to the full Compustat quarterly data downloaded from WRDS 

***/

/* Define directory path in macro variable */
%let dir = "/path/to/your/directory";
/* Define libraries that contain Compustat and Dealscan data after downloading from WRDS. */
libname mycomp "&dir/comp";
libname dealsca "&dir/dealscan";

*** ------------------------------------------------------------------***; 


* HERE I GENERATE AND KEEP ALL THE VARIABLES I NEED;
Data mycomp.comp_fundq4;
set comp_fundq3x;
if prccq>0 and cshoq then EQUITY=prccq*cshoq;
if SEQQ>0  then NETWORTH2=SEQQ; /* #11 NET WORTH = Shareholder Equity */
if ATQ > 0 then lnAT=log(ATQ);
if (dlttq >.) or (dlcq >.) then TotalDebt=( dlcq+dlttq ) ;	/*long term debt and current liabilities*/
if q = 1 then capxq=capxy;
if q in (2,3,4) then capxq=capxY-capxyt_1; /* Getting quarterly data for capital expenditures -Continues on the next step as well */
if ATQ > 0 then MB=( (prccq*cshoq) + TotalDebt + Coalesce(pstkq,0)-Coalesce(txditcq,0))/ATQ;
if ltq > 0 then MB2=( (prccq*cshoq)/ltq );
if ATQ > 0 then BL=TotalDebt/ATQ;
if ATQ > 0 then TAN=PPENTQ/ATQ;
if ATQ > 0 then ROA=oibdpq/ATQ;
if equity>0 then ROE=NIQ/equity;
if ATQ > 0 then CH=CHEQ/ATQ;
if saleq>0 then profit_margin=niq/saleq;
IF SALEQ > 0 THEN ROS=OIBDPQ/SALEQ;
IF atq > 0 then COLLATERAL = (invtq + PPENTQ)/ATQ;
if atq > 0 and ltq > 0 then  AltmanZ = 1.2*(actq-lctq)/atq + 1.4*req/atq + 3.3*(NIQ+XINTQ+TXTQ)/atq + 0.6*cshoq*prccq/ltq + 0.999*saleq/atq; /* Z-SCORE*/
if atq>0 then tangnetworth=(atq-ltq-Coalesce(intanq,0)); /* #10 TANGIBLE NET WORTH*/
if atq >0 then debt_tangnetworth= TotalDebt /(atq-ltq - Coalesce(intanq,0)); /* #4 DEBT/TANGIBLE NET WORTH */
if lctq > 0 then cur_ratio=actq/lctq;		/* #6 CURRENT RATIO = Current Assets / Current Liabilities */
if SEQQ > 0 then debt_equity = (dlttq + dlcq)/SEQQ; /* #3 DEBT/EQUITY */
if lctq > 0 then quick_ratio = (actq-Coalesce(invfgq,0))/lctq; /* #9 QUICK RATIO */
DateStart=intnx('month', datadate, 1, 'B');
DateEnd_lead=coalesce(DateEnd_lead, intnx('month', datadate, 3, 'E'));
DateEnd_lag=coalesce(DateEnd_lag, intnx('month', datadate, 3, 'E'));
*** VARIABLES TO KEEP  ;
keep GVkey Datadate fyearq fqtr DateEnd_lead DateEnd_lag DateStart SPLTICRM SIC ATq lnAT EQUITY AltmanZ 
MB MB2 BL TAN ROA ROE profit_margin CH ROS ROE  COS_SL SL_AT COLLATERAL 
ibq dpq dvy dd1q ipodate 

capxq capxy xrdq xrdy ppentq saleq oibdpq XSGAY prccq NETWORTH2 debt_equity tangnetworth debt_tangnetworth cur_ratio  quick_ratio  /* For project with Morad and Billy */ 
dlcq DLTTQ  cshoq prccq ltq XINTQ  actq lctq sppey niq totalDebt;

format datestart DateEnd_lead  mmddyy10.;
run;

 
************;
filename junk dummy; /* stop the log to avoid warnings choking the log file due to warning me about missing values */
proc printto log=junk;run; 
PROC EXPAND data=mycomp.comp_fundq4 OUT=comp_fundq5 METHOD=NONE; 
BY Gvkey;
convert capxq=capxq4   /   transformout=(movsum 4 ); /* #1: CAPITAL EXPENDITURES */
convert xrdq=xrdq4   /   transformout=(movsum 4 ); /* #1: CAPITAL EXPENDITURES */
convert oibdpq=oibdpq_roll  /   transformout = (movsum 4 ); /* #7 EBITDA  */
convert niq=niq_roll  /   transformout = (movsum 4 set (-9999 .)); /*  Rolling Net Income  */
convert saleq=saleq_roll  /   transformout = (movsum 4 set (-9999 .)); /*  Rolling Sales  */
convert XINTQ=XINTQ4     /   transformout = (movsum 4 set (-9999 .)); 
convert dlcq=dlcqt_1   / transformin=(setmiss -9999)   transformout = ( LAG 4 set (-9999 .) );
convert roa=roat_1      / transformin=(setmiss -9999) transformout = (lag 4 set (-9999 .));
convert roa=roa_1yafter / transformin=(setmiss -9999) transformout = (lead 4 set (-9999 .));
convert TotalDebt=TotalDebt_1 / transformin=(setmiss -9999) transformout = (lag 4 set (-9999 .));
convert atq=atq_1 / transformin=(setmiss -9999) transformout = (lag 4 set (-9999 .));
ID DATADATE;
run;
proc printto;run; /* Reactive the log */

* Generate more variables that needed me to have the rolling sums;
data comp_fundq6;
set comp_fundq5;
if oibdpq_roll >0 then EBITDA=oibdpq_roll; /* #7 EBITDA  */
IF DLTTQ > 0 THEN EBITDA_DEBT= EBITDA / totalDebt ; /* #2 EBITDA/DEBT */
IF (XINTQ4+dlcqt_1) > 0 THEN FX_CH_COV= EBITDA / (XINTQ4+dlcqt_1) ; /* #8 FIXED CHARGE COVERAGE */
IF XINTQ4 > 0 THEN INT_COV = EBITDA / XINTQ4 ; /* #5 INTEREST COVERAGE  */
if atq_1 > 0 then Debt_Issuance = (TotalDebt-TotalDebt_1)/atq_1;
if atq>0 and atq_1>0 then Asset_growth=atq-atq_1;
run;

data comp_fundq6;
	retain gvkey datadate atq capxq4 EBITDA EBITDA_DEBT FX_CH_COV INT_COV NETWORTH2 tangnetworth debt_tangnetworth cur_ratio debt_equity quick_ratio ;
	set comp_fundq6;
run;	

proc export data = comp_fundq6
			file = "&dir/comp_fundq6.dta"
			dbms = STATA REPLACE;
run;

*** -----------------------------------------------------------------------***;
*** From this point on I'm working on getting info on the actual covenants ***;
*** -----------------------------------------------------------------------***;
*** NOTE: dealsca is the library in Dealscan that contains the following Dealscan datasets: 
	- facility
	- financialcovenant
	- Networthcovenant
***;

* Fix the CapeX and EBITDA covenants; 
data financialcovenant;
	set dealsca.financialcovenant;
	if covenantType in("Max. Capex", "Min. EBITDA") then InitialRatio=InitialAmt;
run;


proc transpose data=financialcovenant(keep=PackageID CovenantType InitialRatio InitialAmt) out=covenants1(drop=_NAME_) ;
   var InitialRatio ;
   by packageid ;
   ID CovenantType;
run;

data covenants_2(keep=PackageID CovenantType baseamt);
set dealsca.Networthcovenant;
if covenantType='NA' then delete;
run;
proc sort data=covenants_2; by packageid; run;
proc transpose data=covenants_2 out=covenants_2(drop=_NAME_) ;
   var baseamt;
   by packageid;
   ID CovenantType;
run;
* merge to get all the covenants you need in one file ;
proc sql;
	create table dealsca.covenants as
	select distinct a.*, b.Tangible_Net_Worth/1000000 as Tangible_net_worth, b.Net_worth/1000000 as Net_worth, coalesce(b.Net_worth/1000000, b.Tangible_Net_Worth/1000000) as networth_combo
	from covenants1 as a left join covenants_2 as b on a.packageid=b.packageid
	order by packageid;
quit;

* The 11 ratios from dealscan;
proc sql;
	create table covenants as 
	select distinct packageid, 
	Min__Interest_Coverage as min_int_cov, 
	Min__Current_Ratio as min_cur_ratio, 
	case when Max__Debt_to_EBITDA>0 then (1 / Max__Debt_to_EBITDA) else . end as min_ebitda_debt, 
	Max__Debt_to_Tangible_Net_Worth as max_debt_tangnetworth,  
	Max__Leverage_ratio as max_leverage_ratio, 
	Min__Quick_Ratio as min_quick_ratio, 
	Max__Debt_to_Equity as max_debt_equity, 
	 Max__Capex/1000000 as max_capex, 
	Min__EBITDA/1000000 as min_ebitda, 
	Min__Fixed_Charge_Coverage as min_fixed_charge_coverage, 
	tangible_net_worth as tangible_net_worth_d, 
	Net_worth as net_worth_d, 
	networth_combo as min_networth         
	from dealsca.covenants /* before I change it I was using: dealscan.covenants2 */ 
	order by packageid;
quit;




*** -----------------------------------------------------------------------***;
***  Merge Compustat ratios with loan covenants (and actual violations)	   ***;
***  Data requirements: 
	- "cov_violations" (Data on Covenant Violations that Nini Smith and Sufi shared)
*** -----------------------------------------------------------------------***;


*** Match financial covenants with GVKEY information;
 proc sql;
    create table covenants_gvkey as
    select distinct a.packageid, max(c.gvkey) as gvkey, sum(b.facilityamt/1000000) as dealamount, 
    				min(b.facilitystartdate)  as min_facilitystartdate format mmddyy10., 
    				max(b.facilityenddate)    as max_facilityenddate format mmddyy10., 
    				a.*
    from covenants as a 
    	left join dealsca.facility as b on a.packageid=b.packageid 
    	left join dealsca.dealscan_link as c on b.borrowercompanyid=c.bcoid
    group by gvkey , a.packageid 
    order by gvkey , a.packageid  
	;
quit; 

** Add 11 covenant ratios from Compustat and compare them to actual ratio; 
 proc sql;
    create table comp_covenants as
    select distinct  a.*,
    				  max(b.min_int_cov) as min_int_cov, max(b.min_cur_ratio) as min_cur_ratio,  max(b.min_ebitda_debt) as min_ebitda_debt,  max(b.min_quick_ratio) as min_quick_ratio,  max(b.min_ebitda) as min_ebitda,  max(b.min_fixed_charge_coverage) as min_fixed_charge_coverage,  max(b.tangible_net_worth_d) as tangible_net_worth_d,  max(b.net_worth_d) as net_worth_d, min(b.max_debt_tangnetworth) as max_debt_tangnetworth,  min(b.max_leverage_ratio) as max_leverage_ratio,  min(b.max_debt_equity) as max_debt_equity,  min(b.max_capex) as max_capex, max(min_networth) as min_networth,
    				  min(b.packageid) as packageid_min,
    				  max(d.viol_confirmed) as viol_confirmed

    from comp_fundq6 (where=(year(datadate) ge 1993 and year(datadate) le 2019 )) as a 
    	left join covenants_gvkey as b on input(a.gvkey, best12.)=b.gvkey and a.datadate between b.min_facilitystartdate and b.max_facilityenddate
    	left join dealsca.cov_violations_nini2016 as d on input(a.gvkey, best12.)=d.gvkey and a.datadate=d.datadate
    group by a.gvkey, a.datadate
    having b.min_facilitystartdate=max(b.min_facilitystartdate)
    order by a.gvkey, a.datadate
	;

   create table comp_covenants1 as
   select distinct a.*, max(case when b.gvkey is not null then 1 else 0 end) as in_dealscan
   from comp_covenants as a 
        	left join dealsca.dealscan_link_2017 as b on input(a.gvkey, best12.)=b.gvkey 
   group by a.gvkey 
   order by a.gvkey , a.datadate
 		;
quit; 

** Create slackness variables: ;
data comp_covenants2;
	set comp_covenants1;
	if min_int_cov > 0 and int_cov > 0 							then slack_int_cov 				= int_cov 				 	-min_int_cov ; 	  		  		else slack_int_cov  		  = 99;
	if min_cur_ratio > 0 and cur_ratio > 0 						then slack_cur_ratio 			= cur_ratio 			 	-min_cur_ratio ; 	  		  	else slack_cur_ratio 		  = 99;
	if min_ebitda_debt > 0 and EBITDA_DEBT > 0 					then slack_ebitda_debt 			= ebitda_debt 			 	-min_ebitda_debt ;   		  	else slack_ebitda_debt 	  		= 99;
	if max_debt_tangnetworth > 0 and debt_tangnetworth > 0  	then slack_debt_tangnetworth	= max_debt_tangnetworth 	-debt_tangnetworth ; 		  	else slack_debt_tangnetworth= 99;
	if min_fixed_charge_coverage > 0 and FX_CH_COV > 0 			then slack_fx_ch_cov 			= FX_CH_COV 			 	-min_fixed_charge_coverage ; 	else slack_fx_ch_cov 		  = 99;
	if min_quick_ratio > 0 and quick_ratio > 0 					then slack_quick_ratio 			= quick_ratio 			 	-min_quick_ratio ; 		  		else slack_quick_ratio 	  = 99;
	if max_debt_equity > 0 and debt_equity > 0 					then slack_debt_equity 			= max_debt_equity 			-debt_equity ; 			  		else slack_debt_equity 	  = 99;
	if max_capex > 0 and capxq4 > 0 							then slack_capex 				= max_capex 			 	-capxq4 ; 					  	else slack_capex 			  = 99;
	if min_ebitda > 0 and ebitda>0 								then slack_ebitda 				= ebitda 				 	-min_ebitda ; 				  	else slack_ebitda 		  = 99;
	if min_networth > 0 							 			then slack_networth 			= networth2 		 	 	-min_networth ; 	  		  	else slack_networth 	  	  = 99;
	if tangible_net_worth_d > 0 and tangnetworth > 0 			then slack_tangnetworth 		= tangnetworth 		 		-tangible_net_worth_d ; 	  	else slack_tangnetworth 	  = 99;
	if net_worth_d > 0 and networth2 > 0 						then slack_net_worth 			= networth2 			 	-net_worth_d ; 			  		else slack_net_worth 		  = 99;
    if max_leverage_ratio > 0 and BL > 0 						then slack_BL 					= max_leverage_ratio 		-BL ;						  	else slack_BL  			  = 99;	
/* This covenant is not used by Murfin for some reason */
** VIOLATION DUMMIES **; 
	violation_short=0;
	if slack_tangnetworth<0 or slack_net_worth<0 or slack_cur_ratio<0 then violation_short=1; 
	violation_short2=0;
	if slack_networth<0 or slack_cur_ratio<0 then violation_short2=1; 
	violation_all=0; 
	if slack_int_cov<0 or slack_cur_ratio<0 or slack_ebitda_debt<0 or slack_debt_tangnetworth<0 
	  or slack_fx_ch_cov<0 or slack_quick_ratio<0 or slack_debt_equity<0 or slack_capex<0 
	    or slack_ebitda<0 or slack_tangnetworth<0 or slack_net_worth<0 then violation_all=1; 
run;


 proc means data=comp_covenants2 N mean std min p1 p10 p25 p50 p75 p90 p99 max  ;
 	var viol_confirmed violation_short violation_all in_dealscan;
 run; 

 proc sql;
    create table compustat_addons as
    select distinct input(gvkey, best12.) as gvkey, fyear, dd1 
    from mycomp.funda 
    order by gvkey, fyear 
	;
quit; 

Proc sort nodupkey data=compustat_addons; by gvkey fyear; run;

proc export data = compustat_addons
			file = "&path./compustat_addons.dta"
			dbms = STATA REPLACE;
run;

 
*** -----------------------------------------------------------------------***;
***  Export DATASET to Stata and have fun 							 	   ***;
*** -----------------------------------------------------------------------***;

proc export data = comp_covenants2
			file = "&path./comp_covenants.dta"
			dbms = STATA REPLACE;
run;

*** For the remaining constructions of the quarterly data, use the Stata code file: '0_Sample_Creation_Quarterly.do' ***;